package fr.utbm.gi.in56.ads;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.LinkedList;
import java.util.List;

import fr.utbm.gi.in56.database.DatabaseManager;
import fr.utbm.gi.in56.error.ErrorType;

/**
 * 
 * Implementation of an adsSale manager.
 * @author Adrien Hoffer & Tarik Belabbas
 *
 */
public class AdsManager extends DatabaseManager
{
	/**
	 * Parameterized constructor.
	 * 
	 * @param _connection the connection to use to create a statement
	 * @throws SQLException if the connection cannot create a statement
	 */
	public AdsManager( Connection _connection )
	{
		super( _connection );
	}
	
	/**
	 * Changes the state of an ad and add the  secondUser if necessary
	 * 
	 * @param _idAd the id of the ad
	 * @param _adType the type of the ad
	 * @param _state the new state
	 * @param _secondUserLogin the second user login
	 * @return ErrorType.NONE if success
	 */
	public ErrorType changeAdState( int _idAd, AdType _adType,
			AdState _state, String _secondUserLogin )
	{
		ErrorType result = ErrorType.NONE;
		
		Statement statement = this.getNewStatement();
		
		try
		{
			statement.executeUpdate( "UPDATE ads_" + _adType.getType() + //$NON-NLS-1$
					" SET state = '" + _state.ordinal() + "' WHERE " + //$NON-NLS-1$ //$NON-NLS-2$
					"id_ads_" + _adType.getType() + " ='" + _idAd + "'"); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
			
			if( ! _secondUserLogin.isEmpty() )
			{
				String login = ( _adType == AdType.BUY ) ? "login_seller" : "login_buyer"; //$NON-NLS-1$ //$NON-NLS-2$
				
				statement.executeUpdate( "UPDATE ads_" + _adType.getType() + //$NON-NLS-1$
					" SET " + login + " = '" + _secondUserLogin + "' WHERE " + //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
					"id_ads_" + _adType.getType() + " ='" + _idAd + "'"); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
			}
		}
		catch ( SQLException e)
		{
			result = ErrorType.DB_ERROR;
			e.printStackTrace();
		}
		
		return result;
	}
	
	/**
	 * Delete an ad
	 * 
	 * @param _idAd the id of the ad
	 * @param _adType the type of ad
	 * @return ErrorType.NONE if success
	 */
	public ErrorType deleteAd( int _idAd, AdType _adType )
	{
		//TODO: /!\ Delete the image if there is one corresponding
		ErrorType result = ErrorType.NONE;
		
		Statement statement = this.getNewStatement();
		
		try
		{
			statement.executeUpdate( "DELETE FROM ads_" + _adType.getType() +  //$NON-NLS-1$
					" WHERE id_ads_" + _adType.getType() + "='" + _idAd + "'"); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
		}
		catch( SQLException e )
		{
			result = ErrorType.DB_ERROR;
			e.printStackTrace();
		}
		
		return result;
	}
	
	/**
	 * Posts a sale ad by inserting the ad in the database
	 * 
	 * @param _title the title of the ad
	 * @param _seller the login of the seller
	 * @param _description the description of the ad
	 * @param _carBrand the brand id (0 if no id)
	 * @param _carModel the model id (0 if no id)
	 * @param _automatic 0 if the car is automatic, 1 else, 2 if disabled
	 * @param _price the price of the car
	 * @param _vehicleYear the year of entry into service of the car (0 if disable)
	 * @param _kmNb the number of kilometers of the car
	 * @param _energy the id of the type of energy of the car (0 if disabled)
	 * @param _hasImage <tt>true</tt> if there if an image previously uploaded
	 * @return either the id of the created ad if success, either -1
	 */
	public int postSellAd( String _title, String _seller, String _description,
			int _carBrand, int _carModel, int _automatic, int _price,
			int _vehicleYear, int _kmNb, int _energy, boolean _hasImage )
	{	
		int adId = 0;
		
		Statement statement = this.getNewStatement();
		
		String sqlQuery = new String("INSERT INTO `ads_sale` (`login_seller`, `title` "); //$NON-NLS-1$
		
		String sqlQueryValues = new String(" VALUES ('" + _seller.replace( "'", "''" ) + //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
				"', '" + _title.replace( "'", "''" ) + "'"); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$
		
		if( ! _description.isEmpty() )
		{
			sqlQuery += ",`description`"; //$NON-NLS-1$
			sqlQueryValues += ",'" + _description.replace( "'", "''" ) + "'"; //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$
		}
		
		if( _carBrand != 0 )
		{
			sqlQuery += ",`car_brand`"; //$NON-NLS-1$
			sqlQueryValues += ",'" + _carBrand + "'"; //$NON-NLS-1$ //$NON-NLS-2$
		}
		
		if( _carModel != 0 )
		{
			sqlQuery += ",`car_model`"; //$NON-NLS-1$
			sqlQueryValues += ",'" + _carModel + "'"; //$NON-NLS-1$ //$NON-NLS-2$
		}
		
		if( _automatic != 2 )
		{
			sqlQuery += ",`is_automatic`"; //$NON-NLS-1$
			sqlQueryValues += ",'" + _automatic + "'"; //$NON-NLS-1$ //$NON-NLS-2$
		}
		
		if( _price != -1 )
		{
			sqlQuery += ",`price`"; //$NON-NLS-1$
			sqlQueryValues += ",'" + _price + "'"; //$NON-NLS-1$ //$NON-NLS-2$
		}
		
		if( _vehicleYear != 0 )
		{
			sqlQuery += ",`vehicle_year`"; //$NON-NLS-1$
			sqlQueryValues += ",'" + _vehicleYear + "'"; //$NON-NLS-1$ //$NON-NLS-2$
		}
		
		if( _kmNb != -1 )
		{
			sqlQuery += ",`km_nb`"; //$NON-NLS-1$
			sqlQueryValues += ",'" + _kmNb + "'"; //$NON-NLS-1$ //$NON-NLS-2$
		}
		
		if( _energy != 0 )
		{
			sqlQuery += ",`energy`"; //$NON-NLS-1$
			sqlQueryValues += ",'" + _energy + "'"; //$NON-NLS-1$ //$NON-NLS-2$
		}
		
		sqlQuery += ",`has_image`"; //$NON-NLS-1$
		int img = (_hasImage) ? 1 : 0;
		sqlQueryValues += ",'" + img + "'"; //$NON-NLS-1$ //$NON-NLS-2$
		
		sqlQuery += ")" + sqlQueryValues + ")"; //$NON-NLS-1$ //$NON-NLS-2$
		
		
		// Returns the id of the created ad
		ResultSet rst = null;
		
		try
		{
			statement.executeUpdate( sqlQuery, Statement.RETURN_GENERATED_KEYS );
			rst = statement.getGeneratedKeys();
			if (rst.next())
			{
		        adId = rst.getInt(1);
			}
			rst.close();
		}
		catch ( SQLException e ){
			adId = -1;
			e.printStackTrace();
		}
		
		return adId;
	}	
	
	/**
	 * Returns the sell ads corresponding to the parameters, sorted by date of post
	 * (the most recently ads are the first returned)
	 * 
	 * 
	 * @param _carBrand the brand id (0 if no id)
	 * @param _carModel the model id (0 if no id)
	 * @param _automatic 0 if the car is automatic, 1 else, 2 if disabled
	 * @param _priceMin the minimum price of the car (-1 if disabled)
	 * @param _priceMax the maximum price of the car (-1 if disabled)
	 * @param _vehicleYearMin the minimum year of entry into service of the car (0 if disable)
	 * @param _vehicleYearMax the maximum year of entry into service of the car (0 if disable)
	 * @param _kmNbMin the minimum number of kilometers of the car (-1 if disabled)
	 * @param _kmNbMax the maximum number of kilometers of the car (-1 if disabled)
	 * @param _energy the id of the type of energy of the car (0 if disabled)
	 * 
	 * @param _nbAds the number of ads to return (0 = all)
	 * @param _offset where begins the first returned add (default = 0)
	 * 		i.e. if _nb = 5 and _offset = 8, the ads in position from 9 to 13 in
	 * 		the request results are returned 
	 * 
	 * @param _notChecked <tt>true</tt> to returns the ads not checked
	 * @param _state the method returns the ads corresponding to this state (-1 if disabled)
	 * @param _notThisState the ads with this state won't be returned (-1 if disabled)
	 * @param _county the method returns the ads corresponding to this county (0 if disabled)
	 * @param _loginSeller the login of the seller (empty String if disabled)
	 * @param _loginBuyer the login of the buyer (empty String if disabled)
	 * @return a List of all the corresponding ads with the interesting join attributes
	 */
	public List<AdSell> getSellAds( int _carBrand, int _carModel,	int _automatic,
			int _priceMin, int _priceMax, int _vehicleYearMin, int _vehicleYearMax,
			int _kmNbMin, int _kmNbMax, int _energy, int _nbAds, int _offset,
			boolean _notChecked, int _state, int _notThisState, int _county,
			String _loginBuyer, String _loginSeller)
	{
		Statement statement = this.getNewStatement();
		ResultSet rst =null;
		
		List<AdSell> adsSell = new LinkedList< AdSell >();
		
		String sqlQuery = new String("SELECT ads_sale.*, users.county, users.global_rate, "+ //$NON-NLS-1$
				"(SELECT energy.energy FROM energy WHERE energy.id_energy = ads_sale.energy) AS energyStr,"+ //$NON-NLS-1$
				"(SELECT car_brands.brand FROM car_brands WHERE car_brands.id_car_brands = ads_sale.car_brand) AS brandStr," + //$NON-NLS-1$
				"(SELECT car_models.model FROM car_models WHERE car_models.id_car_models = ads_sale.car_model) AS modelStr" +  //$NON-NLS-1$
				" FROM ads_sale INNER JOIN users ON users.login = ads_sale.login_seller "); //$NON-NLS-1$
		
		// If the clause where must be write...
		if( _carBrand != 0 || _carModel != 0 || _automatic != 2 || _priceMin != -1 ||
				_priceMax != -1 || _vehicleYearMin != 0 || _vehicleYearMax != 0 ||
				_kmNbMin != -1 || _kmNbMax != -1 || _energy != 0 || _notChecked ||
				_state != -1 || _notThisState != -1 || _county != 0 || ! _loginBuyer.isEmpty() ||
				! _loginSeller.isEmpty() )
		{
			sqlQuery += " WHERE "; //$NON-NLS-1$
			
			if( _carBrand != 0 )
			{
				sqlQuery += " ads_sale.car_brand = '" + _carBrand + "' AND"; //$NON-NLS-1$ //$NON-NLS-2$
			}
			
			if( _carModel != 0 )
			{
				sqlQuery += " ads_sale.car_model = '" + _carModel + "' AND"; //$NON-NLS-1$ //$NON-NLS-2$
			}
			
			if( _automatic != 2 )
			{
				sqlQuery += " ads_sale.is_automatic = '" + _automatic + "' AND"; //$NON-NLS-1$ //$NON-NLS-2$
			}
			
			if( _priceMin != -1 )
			{
				sqlQuery += " ads_sale.price > '" + _priceMin + "' AND"; //$NON-NLS-1$ //$NON-NLS-2$
			}
			
			if( _priceMax != -1 )
			{
				sqlQuery += " ads_sale.price < '" + _priceMax + "' AND"; //$NON-NLS-1$ //$NON-NLS-2$
			}
			
			if(  _vehicleYearMin != 0 )
			{
				sqlQuery += " ads_sale.vehicle_year > '" + _vehicleYearMin + "' AND"; //$NON-NLS-1$ //$NON-NLS-2$
			}
			
			if( _vehicleYearMax != 0 )
			{
				sqlQuery += " ads_sale.vehicle_year < '" + _vehicleYearMax + "' AND"; //$NON-NLS-1$ //$NON-NLS-2$
			}
			
			if( _kmNbMin != -1 )
			{
				sqlQuery += " ads_sale.km_nb > '" + _kmNbMin + "' AND"; //$NON-NLS-1$ //$NON-NLS-2$
			}
			
			if( _kmNbMax != -1 )
			{
				sqlQuery += " ads_sale.km_nb < '" + _kmNbMax + "' AND"; //$NON-NLS-1$ //$NON-NLS-2$
			}
			
			if(  _energy != 0 )
			{
				sqlQuery += " ads_sale.energy = '" + _energy + "' AND"; //$NON-NLS-1$ //$NON-NLS-2$
			}
			
			if( _notChecked )
			{
				sqlQuery += " ads_sale.checked = '0' AND"; //$NON-NLS-1$
			}
			
			if( _state != -1 )
			{
				sqlQuery += " ads_sale.state = '" + _state + "' AND"; //$NON-NLS-1$ //$NON-NLS-2$
			}
			
			if( _notThisState != -1 )
			{
				sqlQuery += " ads_sale.state <> '" + _notThisState + "' AND"; //$NON-NLS-1$ //$NON-NLS-2$
			}
			
			if( _county != 0 )
			{
				sqlQuery += " users.county = '" + _county + "' AND"; //$NON-NLS-1$ //$NON-NLS-2$
			}
			
			if( ! _loginBuyer.isEmpty() )
			{
				sqlQuery += " ads_sale.login_buyer = '" + _loginBuyer.replace( "'", "''" ) + "' AND"; //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$
			}
			
			if( ! _loginSeller.isEmpty() )
			{
				sqlQuery += " ads_sale.login_seller = '" + _loginSeller.replace( "'", "''" ) + "' AND"; //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$
			}
			
			sqlQuery = sqlQuery.substring( 0, sqlQuery.length() - 3 );
		}
		
		// Order By clause
		sqlQuery += " ORDER BY ad_date DESC "; //$NON-NLS-1$
		
		// Number of results an offset: LIMIT clause
		if( _nbAds !=0 )
		{
			sqlQuery += " LIMIT " + _offset + ", " + _nbAds; //$NON-NLS-1$ //$NON-NLS-2$
		}
		
		// Execute the query
		try
		{
			rst = statement.executeQuery( sqlQuery );
			
			// Fill in the List with the resultSet
			while( rst.next() )
			{
				String carBrand 	= rst.getString( "brandStr" ); //$NON-NLS-1$
				String carModel 	= rst.getString( "modelStr" ); //$NON-NLS-1$
				String description 	= rst.getString( "description" ); //$NON-NLS-1$
				String energy		= rst.getString( "energyStr" );	//$NON-NLS-1$
				String loginBuyer  = rst.getString( "login_buyer" ); //$NON-NLS-1$
				boolean hasImage 	= ( rst.getInt( "has_image" ) == 0) ? false : true; //$NON-NLS-1$
				boolean checked		= ( rst.getInt( "checked" ) == 0) ? false : true; //$NON-NLS-1$
					
				if( carBrand == null ) //$NON-NLS-1$
				{
					carBrand = ""; //$NON-NLS-1$
				}
				
				if( carModel == null ) //$NON-NLS-1$
				{
					carModel = ""; //$NON-NLS-1$
				}
				
				if( description == null ) //$NON-NLS-1$
				{
					description = ""; //$NON-NLS-1$
				}
				
				if( energy == null ) //$NON-NLS-1$
				{
					energy = ""; //$NON-NLS-1$
				}
				
				if( loginBuyer == null ) //$NON-NLS-1$
				{
					loginBuyer = ""; //$NON-NLS-1$
				}
				
				adsSell.add( new AdSell( rst.getInt( "id_ads_sale" ), //$NON-NLS-1$
						rst.getDate( "ad_date" ), rst.getInt( "state" ), //$NON-NLS-1$ //$NON-NLS-2$
						rst.getString( "title" ), rst.getInt( "car_brand" ), //$NON-NLS-1$ //$NON-NLS-2$
						carBrand, rst.getInt( "car_model" ), carModel, //$NON-NLS-1$
						description, rst.getInt( "energy" ), energy, //$NON-NLS-1$
						rst.getInt( "is_automatic" ), checked, loginBuyer, //$NON-NLS-1$ //$NON-NLS-2$
						rst.getString( "login_seller" ), rst.getInt( "county" ), rst.getInt( "global_rate" ), //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
						rst.getInt( "vehicle_year" ), rst.getInt( "km_nb" ), //$NON-NLS-1$ //$NON-NLS-2$
						rst.getInt( "price" ), hasImage ) ); //$NON-NLS-1$
			}
			
			rst.close();
		}
		catch ( SQLException e ){
			e.printStackTrace();
		}
		
		return adsSell;
	}
	
	/**
	 * Returns a list of the sell ads  bought by an user
	 * 
	 * @param _userLogin the login of the user
	 * @return a list of the sell ads bought the user
	 */
	public List< AdSell > getSellAdsBoughtByUser( String _userLogin )
	{
		return getSellAds( 0, 0, 2, -1, -1, 0, 0, -1, -1, 0, 0, 0, false, -1,
				-1, 0, _userLogin, "" ); //$NON-NLS-1$
	}
	
	/**
	 * Returns a list of the sell ads posted by an user
	 * 
	 * @param _userLogin the login of the user
	 * @return a list of the sell ads posted by the user
	 */
	public List< AdSell > getSellAdsPostedByUser( String _userLogin )
	{
		return getSellAds( 0, 0, 2, -1, -1, 0, 0, -1, -1, 0, 0, 0, false, -1,
				-1, 0, "", _userLogin ); //$NON-NLS-1$
	}
	
	/**
	 * Returns an AdBuy corresponding to an ad id
	 * 
	 * @param _adid the ad id
	 * @return an AdBuy corresponding to the ad id
	 */
	public AdBuy getAdBuy( int _adid )
	{
		Statement statement = getNewStatement();
		AdBuy adBuy = null;
		try
		{
			ResultSet rst = statement.executeQuery( "SELECT * FROM ads_buy " +
					"WHERE id_ads_buy = '" + _adid + "'");
			
			if( rst.first() )
			{
				int automatic = (rst.getInt( "is_automatic" ) == -1) ? 2:rst.getInt( "is_automatic" );
				boolean checked = (rst.getInt( "checked" ) == 0 ) ? false : true;
				adBuy = new AdBuy( _adid, rst.getDate( "ad_date" ),
						rst.getInt( "state" ), rst.getString( "title" ),
						rst.getInt( "car_brand" ), "", rst.getInt( "car_model" ), "",
						rst.getString( "description" ), rst.getInt( "energy" ), "",
						automatic, checked,
						rst.getString( "login_buyer" ), rst.getString( "login_seller" ),
						0, -1, rst.getInt( "km_nb_min" ), rst.getInt( "km_nb_max" ),
						rst.getInt( "price_min" ), rst.getInt( "km_nb_max" ),
						rst.getInt( "vehicle_year_min" ), rst.getInt( "vehicle_year_min" ) );
			}
			rst.close();
		}
		catch( SQLException e)
		{
			e.printStackTrace();
		}
		return adBuy;
	}
	
	/**
	 * Returns an AdSell corresponding to an ad id
	 * 
	 * @param _adid the ad id
	 * @return an AdSell corresponding to the ad id
	 */
	public AdSell getAdSell( int _adid )
	{
		Statement statement = getNewStatement();
		AdSell adSell = null;
		try
		{
			ResultSet rst = statement.executeQuery( "SELECT * FROM ads_sale " +
					"WHERE id_ads_sale = '" + _adid + "'");
			
			if( rst.first() )
			{
				int automatic = (rst.getInt( "is_automatic" ) == -1 || rst.getInt( "is_automatic" ) == 0 ) ? 2:rst.getInt( "is_automatic" );
				boolean checked = (rst.getInt( "checked" ) == 0 ) ? false : true;
				
				adSell = new AdSell( _adid, rst.getDate( "ad_date" ),
						rst.getInt( "state" ), rst.getString( "title" ),
						rst.getInt( "car_brand" ), "", rst.getInt( "car_model" ),"",
						rst.getString( "description" ), rst.getInt( "energy" ), "",
						automatic, checked, rst.getString( "login_buyer" ),rst.getString( "login_seller" ),
						0, -1, rst.getInt( "vehicle_year" ), rst.getInt( "km_nb" ),
						rst.getInt( "price" ), false );
			}
			rst.close();
		}
		catch( SQLException e)
		{
			e.printStackTrace();
		}
		return adSell;
	}
	
	/**
	 * Returns the ads corresponding to the parameters, sorted by date of post
	 * (the most recently ads are the first returned)
	 * 
	 * @param _carBrand the brand id (0 if no id)
	 * @param _carModel the model id (0 if no id)
	 * @param _automatic 0 if the car is automatic, 1 else, 2 if disabled
	 * @param _price price of the car (-1 if disabled)
	 * @param _vehicleYear the year of entry into service of the car (0 if disable)
	 * @param _kmNb the number of kilometers of the car (-1 if disabled)
	 * @param _energy the id of the type of energy of the car (0 if disabled)
	 * 
	 * @param _nbAds the number of ads to return (0 = all)
	 * @param _offset where begins the first returned add (default = 0)
	 * 		i.e. if _nb = 5 and _offset = 8, the ads in position from 9 to 13 in
	 * 		the request results are returned 
	 * 
	 * @param _notChecked <tt>true</tt> to returns the ads not checked
	 * @param _state the method returns the ads corresponding to this state (-1 if disabled)
	 * @param _notThisSate the ads with this state won't be returned (-1 if disabled)
	 * @param _county the method returns the ads corresponding to this county (0 if disabled)
	 * @param _loginSeller the login of the seller (empty String if disabled)
	 * @param _loginBuyer the login of the buyer (empty String if disabled)
	 * @return a List of all the corresponding ads with the interesting join attributes
	 */
	public List< AdBuy > getBuyAds( int _carBrand, int _carModel,	int _automatic,
			int _price, int _vehicleYear, int _kmNb, int _energy, int _nbAds,
			int _offset, boolean _notChecked, int _state, int _notThisState,
			int _county, String _loginBuyer, String _loginSeller)
	{
		Statement statement = this.getNewStatement();
		ResultSet rst =null;
		
		List< AdBuy > adsBuy = new LinkedList< AdBuy >();
		
		String sqlQuery = new String("SELECT ads_buy.*, users.county, users.global_rate, "+ //$NON-NLS-1$
				"(SELECT energy.energy FROM energy WHERE energy.id_energy = ads_buy.energy) AS energyStr,"+ //$NON-NLS-1$
				"(SELECT car_brands.brand FROM car_brands WHERE car_brands.id_car_brands = ads_buy.car_brand) AS brandStr," + //$NON-NLS-1$
				"(SELECT car_models.model FROM car_models WHERE car_models.id_car_models = ads_buy.car_model) AS modelStr" +  //$NON-NLS-1$
				" FROM ads_buy INNER JOIN users ON users.login = ads_buy.login_buyer "); //$NON-NLS-1$
		
		// If the clause where must be write...
		if( _carBrand != 0 || _carModel != 0 || _automatic != 2 || _price != -1 ||
				_vehicleYear != 0 || _kmNb != -1 || _energy != 0 || _notChecked ||
				_state != -1 || _notThisState != -1 || _county != 0 ||
				! _loginBuyer.isEmpty() || ! _loginSeller.isEmpty() )
		{
			sqlQuery += " WHERE "; //$NON-NLS-1$
			
			if( _carBrand != 0 )
			{
				sqlQuery += " ads_buy.car_brand = '" + _carBrand + "' AND"; //$NON-NLS-1$ //$NON-NLS-2$
			}
			
			if( _carModel != 0 )
			{
				sqlQuery += " ads_buy.car_model = '" + _carModel + "' AND"; //$NON-NLS-1$ //$NON-NLS-2$
			}
			
			if( _automatic != 2 )
			{
				sqlQuery += " ads_buy.is_automatic = '" + _automatic + "' AND"; //$NON-NLS-1$ //$NON-NLS-2$
			}
			
			if(  _energy != 0 )
			{
				sqlQuery += " ads_buy.energy = '" + _energy + "' AND"; //$NON-NLS-1$ //$NON-NLS-2$
			}
			
			if( _notChecked )
			{
				sqlQuery += " ads_buy.checked = '0' AND"; //$NON-NLS-1$
			}
			
			if( _state != -1 )
			{
				sqlQuery += " ads_buy.state = '" + _state + "' AND"; //$NON-NLS-1$ //$NON-NLS-2$
			}
			
			if( _notThisState != -1 )
			{
				sqlQuery += " ads_buy.state <> '" + _notThisState + "' AND"; //$NON-NLS-1$ //$NON-NLS-2$
			}
			
			if( _county != 0 )
			{
				sqlQuery += " users.county = '" + _county + "' AND"; //$NON-NLS-1$ //$NON-NLS-2$
			}
			
			if( ! _loginBuyer.isEmpty() )
			{
				sqlQuery += " ads_buy.login_buyer = '" + _loginBuyer.replace( "'", "''" ) + "' AND"; //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$
			}
			
			if( ! _loginSeller.isEmpty() )
			{
				sqlQuery += " ads_buy.login_seller = '" + _loginSeller.replace( "'", "''" ) + "' AND"; //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$
			}
			
			sqlQuery = sqlQuery.substring( 0, sqlQuery.length() - 3 );
		}
		
		// Order By clause
		sqlQuery += " ORDER BY ad_date DESC "; //$NON-NLS-1$
		
		// Number of results an offset: LIMIT clause
		if( _nbAds !=0 )
		{
			sqlQuery += " LIMIT " + _offset + ", " + _nbAds; //$NON-NLS-1$ //$NON-NLS-2$
		}
		
		// Execute the query
		try
		{
			rst = statement.executeQuery( sqlQuery );
			
			// Fill in the Map with the resultSet
			while( rst.next() )
			{
				boolean canAdd = true;
				int priceMin 		= rst.getInt( "price_min" ); //$NON-NLS-1$
				int priceMax 		= rst.getInt( "price_max" ); //$NON-NLS-1$
				int vehicleYearMin 	= rst.getInt( "vehicle_year_min" ); //$NON-NLS-1$
				int vehicleYearMax 	= rst.getInt( "vehicle_year_max" ); //$NON-NLS-1$
				int kmNbMin 		= rst.getInt( "km_nb_min" ); //$NON-NLS-1$
				int kmNbMax			= rst.getInt( "km_nb_max" ); //$NON-NLS-1$
				
				// Check the parameters which can't be verified into the request
				// because of the default values
				if( _price != -1 )
				{
					if( ( priceMin != -1 && priceMin > _price ) || 
							( priceMax != -1 && _price > priceMax) )
					{
						canAdd = false;
					}
				}
				
				if( canAdd && _vehicleYear != 0)
				{
					if( ( vehicleYearMin != 0 && _vehicleYear < vehicleYearMin ) ||
							( vehicleYearMax != 0 && _vehicleYear > vehicleYearMax ) )
					{
						canAdd = false;
					}					
				}
				
				if( canAdd && _kmNb != -1 )
				{
					if( ( kmNbMin != -1 && kmNbMin > _kmNb) ||
							( kmNbMax != -1	&& kmNbMax < _kmNb ) )
					{
						canAdd = false;
					}
				}
				
				if ( canAdd )
				{
					String carBrand 	= rst.getString( "brandStr" ); //$NON-NLS-1$
					String carModel 	= rst.getString( "modelStr" ); //$NON-NLS-1$
					String description 	= rst.getString( "description" ); //$NON-NLS-1$
					String energy		= rst.getString( "energyStr" );	//$NON-NLS-1$
					String loginSeller  = rst.getString( "login_seller" ); //$NON-NLS-1$
					boolean checked		= ( rst.getInt( "checked" ) == 0) ? false : true; //$NON-NLS-1$
					
					if( carBrand == null ) //$NON-NLS-1$
					{
						carBrand = ""; //$NON-NLS-1$
					}
					
					if( carModel == null ) //$NON-NLS-1$
					{
						carModel = ""; //$NON-NLS-1$
					}
					
					if( description == null ) //$NON-NLS-1$
					{
						description = ""; //$NON-NLS-1$
					}
					
					if( energy == null ) //$NON-NLS-1$
					{
						energy = ""; //$NON-NLS-1$
					}
					
					if( loginSeller == null ) //$NON-NLS-1$
					{
						loginSeller = ""; //$NON-NLS-1$
					}
					
					adsBuy.add( new AdBuy( rst.getInt( "id_ads_buy" ), rst.getDate( "ad_date" ), //$NON-NLS-1$ //$NON-NLS-2$
							rst.getInt( "state" ), rst.getString( "title" ), //$NON-NLS-1$ //$NON-NLS-2$
							rst.getInt( "car_brand" ), carBrand, rst.getInt( "car_model" ), //$NON-NLS-1$ //$NON-NLS-2$
							carModel, description, rst.getInt( "energy" ), energy, //$NON-NLS-1$
							rst.getInt( "is_automatic" ), checked, rst.getString( "login_buyer" ), //$NON-NLS-1$ //$NON-NLS-2$
							loginSeller, rst.getInt( "county" ), rst.getInt( "global_rate" ), //$NON-NLS-1$ //$NON-NLS-2$
							rst.getInt( "km_nb_min" ), rst.getInt( "km_nb_max" ), //$NON-NLS-1$ //$NON-NLS-2$
							rst.getInt( "price_min" ), rst.getInt( "price_max" ), //$NON-NLS-1$ //$NON-NLS-2$
							rst.getInt( "vehicle_year_min" ), rst.getInt( "vehicle_year_max" ) ) );				 //$NON-NLS-1$ //$NON-NLS-2$
				}
			}
			
			rst.close();
		}
		catch ( SQLException e ){
			e.printStackTrace();
		}
		
		return adsBuy;
	}	
	
	/**
	 * Returns a list of the buy ads where the user had sold his car
	 * 
	 * @param _userLogin the login of the user
	 * @return a list of the buy ads where the user had sold his car
	 */
	public List< AdBuy > getBuyAdsBoughtByUser( String _userLogin )
	{
		return getBuyAds( 0, 0, 2, -1, 0, -1, 0, 0, 0, false, -1, -1, 0, "", _userLogin ); //$NON-NLS-1$
	}
	
	/**
	 * Returns a list of the buy ads posted by an user
	 * 
	 * @param _user the login of the suer
	 * @return a list of the buy posted by the user
	 */
	public List< AdBuy > getBuyAdsPostedByUser( String _userLogin )
	{
		return getBuyAds( 0, 0, 2, -1, 0, -1, 0, 0, 0, false, -1, -1, 0, _userLogin, ""); //$NON-NLS-1$
	}
	
	/**
	 * Posts a buy ad by inserting it in the database
	 * 
	 * @param _title the title of the ad
	 * @param _idBrand the id of the brand 				(0 if no id)
	 * @param _idModel the id of the model 				(0 if no model)
	 * @param _vehicleYearMin the minimum year			(0 if disabled)
	 * @param _vehicleYearMax the maximum year			(0 if disabled)
	 * @param _kmNbMin the minimum number of kilometers	(-1 if disabled)
	 * @param _kmNbMax the maximum number of kilometers (-1 if disabled)
	 * @param _priceMin tte minimum price				(-1 if disabled)
	 * @param _priceMax the maximum price				(-1 if disabled)
	 * @param _idEnergy the id of the type of energy	(0 if disabled)
	 * @param _automatic 0 if the car is automatic, 1 else, 2 if disabled
	 * @param _description the description of the ad
	 * @param _buyer the login of the poster
	 * @return the type of error, ErroType.NONE if there is no error
	 */
	public int postBuyAd( String _title, int _idBrand, int _idModel,
			int _vehicleYearMin, int _vehicleYearMax, int _kmNbMin, int _kmNbMax,
			int _priceMin, int _priceMax, int _idEnergy, int _automatic,
			String _description, String _buyer)
	{
		Statement statement = this.getNewStatement();
		
		String sqlQuery = new String("INSERT INTO `ads_buy` (`login_buyer`, `title` "); //$NON-NLS-1$
		
		String sqlQueryValues = new String(" VALUES ('" + _buyer.replace( "'", "''" ) + //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
				"', '" + _title.replace( "'", "''" ) + "'"); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$
		
		if( _idBrand != 0)
		{
			sqlQuery += ",`car_brand`"; //$NON-NLS-1$
			sqlQueryValues += ",'" + _idBrand + "'"; //$NON-NLS-1$ //$NON-NLS-2$
		}

		if( _idModel != 0)
		{
			sqlQuery += ",`car_model`"; //$NON-NLS-1$
			sqlQueryValues += ",'" + _idModel + "'"; //$NON-NLS-1$ //$NON-NLS-2$
		}
		
		if( _vehicleYearMin != 0 )
		{
			sqlQuery += ",`vehicle_year_min`"; //$NON-NLS-1$
			sqlQueryValues += ",'" + _vehicleYearMin + "'"; //$NON-NLS-1$ //$NON-NLS-2$
		}
		
		if( _vehicleYearMax != 0 )
		{
			sqlQuery += ",`vehicle_year_max`"; //$NON-NLS-1$
			sqlQueryValues += ",'" + _vehicleYearMax + "'"; //$NON-NLS-1$ //$NON-NLS-2$
		}
		
		if( _kmNbMin != -1)
		{
			sqlQuery += ",`km_Nb_Min`"; //$NON-NLS-1$
			sqlQueryValues += ",'" + _kmNbMin + "'"; //$NON-NLS-1$ //$NON-NLS-2$
		}
		
		if( _kmNbMax != -1)
		{
			sqlQuery += ",`km_Nb_Max`"; //$NON-NLS-1$
			sqlQueryValues += ",'" + _kmNbMax + "'"; //$NON-NLS-1$ //$NON-NLS-2$
		}
		
		if( _priceMin != -1)
		{
			sqlQuery += ",`price_min`"; //$NON-NLS-1$
			sqlQueryValues += ",'" + _priceMin + "'"; //$NON-NLS-1$ //$NON-NLS-2$
		}
		
		if( _priceMax != -1)
		{
			sqlQuery += ",`price_max`"; //$NON-NLS-1$
			sqlQueryValues += ",'" + _priceMax + "'"; //$NON-NLS-1$ //$NON-NLS-2$
		}
		
		if( _idEnergy != 0)
		{
			sqlQuery += ",`energy`"; //$NON-NLS-1$
			sqlQueryValues += ",'" + _idEnergy + "'"; //$NON-NLS-1$ //$NON-NLS-2$
		}
		
		if( _automatic != 2)
		{
			sqlQuery += ",`is_automatic`"; //$NON-NLS-1$
			sqlQueryValues += ",'" + _automatic + "'"; //$NON-NLS-1$ //$NON-NLS-2$
		}
		
		if( ! _description.isEmpty() )
		{
			sqlQuery += ",`description`"; //$NON-NLS-1$
			sqlQueryValues += ",'" + _description.replace( "'", "''" ) + "'"; //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$
		}
		
		sqlQuery += ")" + sqlQueryValues + ")"; //$NON-NLS-1$ //$NON-NLS-2$
		
		// Returns the id of the created ad
		ResultSet rst = null;
		
		int adId = -1;
		try
		{
			statement.executeUpdate( sqlQuery, Statement.RETURN_GENERATED_KEYS );
			rst = statement.getGeneratedKeys();
			if (rst.next())
			{
		        adId = rst.getInt(1);
			}
			rst.close();
		}
		catch ( SQLException e ){
			adId = -1;
			e.printStackTrace();
		}
		
		return adId;
	}
	
	/**
	 * Returns the title of a given ad
	 * 
	 * @param _id the id of the ad
	 * @param _type the type of the ad
	 * @return the title of the ad
	 */
	public String getAdTitle( int _id, AdType _type ){
		
		String title = new String(""); //$NON-NLS-1$
		
		String type = _type.getType();
		
		try
		{
			Statement statement = this.getNewStatement();
			ResultSet rst 		= statement.executeQuery( "SELECT * FROM ads_" + //$NON-NLS-1$
					type + " WHERE id_ads_" + type + " = '" + _id + "'"); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
			
			if( rst.first() )
			{
				title = rst.getString( "title" ); //$NON-NLS-1$
			}
			
			rst.close();
		}
		catch ( SQLException e )
		{
			e.printStackTrace();
		}
		
		return title;
	}
}
